/****** Object: View [dbo].[vw_4_3_1]   Script Date: 2014-01-19 21:47:33 ******/
USE [SHMIS];
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_4_3_1]'))
BEGIN
DROP VIEW [dbo].[vw_4_3_1];
END
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[vw_4_3_1]
AS
   SELECT year,
          house,
          equipment,
          total_rent,
          properties,
          total_fee,
          year_duration,
          Calculated_Area,
          Customer_Code,
          CASE calculated_area
             WHEN 0 THEN 0
             ELSE (total_rent + properties) / year_duration / calculated_area
          END
             AS avgfee
     FROM (SELECT year,
                  SUM (house) AS house,
                  SUM (equipment) AS equipment,
                  SUM (house) + SUM (equipment) AS total_rent,
                  SUM (properties) AS properties,
                  SUM (house) + SUM (equipment) + SUM (properties)
                     AS total_fee,
                  sum (totaldays) AS year_duration,
                  Calculated_Area,
                  Customer_Code
             FROM (SELECT year,
                          SUM (house) AS house,
                          SUM (equipment) AS equipment,
                          SUM (house) + SUM (equipment) AS total_rent,
                          SUM (properties) AS properties,
                          SUM (house) + SUM (equipment) + SUM (properties)
                             AS total_fee,
                          duration AS year_duration,
                          Calculated_Area,
                          Customer_Code,
                          totaldays
                     FROM (SELECT SUBSTRING (CAST (ar.Date AS VARCHAR (10)),
                                             1,
                                             4)
                                     AS year,
                                  date,
                                  CASE Amount_Type
                                     WHEN '01' THEN Amount
                                     ELSE 0
                                  END
                                     AS house,
                                  CASE Amount_Type
                                     WHEN '02' THEN Amount
                                     ELSE 0
                                  END
                                     AS equipment,
                                  CASE Amount_Type
                                     WHEN '03' THEN Amount
                                     ELSE 0
                                  END
                                     AS properties,
                                  CAST (
                                       CONVERT (DATETIME, ar.Duration_End)
                                     - CONVERT (DATETIME, ar.Duration_Begin)
                                     + 1 AS INT)
                                     AS totaldays,
                                  ar.Duration_Begin + '~' + ar.Duration_End
                                     AS duration,
                                  mb.Calculated_Area,
                                  ar.Customer_Code
                             FROM dbo.HD_AccountReceivable AS ar
                                  INNER JOIN dbo.BI_MainBusiness AS mb
                                     ON ar.Contract_Num = mb.Contract_Num
                            WHERE     (ar.verified = '1')
                                  AND (ar.deleted = '0')
                                  AND (ar.audited = '1')
                                  AND (mb.Verified = '1')
                                  AND (mb.Deleted = '0')) AS a
                   GROUP BY Customer_Code,
                            year,
                            date,
                            totaldays,
                            Calculated_Area,
                            duration) b
           GROUP BY Customer_Code, year, Calculated_Area) c
GO

